﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class SanPhamDAO
    {
        public List<SanPhamDTO> LayThongTinSanPham()
        {
            string sql = "SELECT * FROM SANPHAM ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> DanhSach = new List<SanPhamDTO>();
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetString(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.SLTon = reader.GetInt32(2);
                spDTO.DonGia = reader.GetInt32(3);
                spDTO.CV_SP = reader.GetInt32(4);
                DanhSach.Add(spDTO);
            }
            conn.Close();
            return DanhSach;

        }
        public List<SanPhamDTO> LayThongTinSanPham(string maSP)
        {
            string sql = "SELECT * FROM SANPHAM WHERE masp= '" + maSP + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            List<SanPhamDTO> DanhSach = new List<SanPhamDTO>();
            while (reader.Read())
            {
                SanPhamDTO spDTO = new SanPhamDTO();
                spDTO.MaSP = reader.GetString(0);
                spDTO.TenSP = reader.GetString(1);
                spDTO.SLTon = reader.GetInt32(2);
                spDTO.DonGia = reader.GetInt32(3);
                spDTO.CV_SP = reader.GetInt32(4);
                DanhSach.Add(spDTO);
            }
            conn.Close();
            return DanhSach;
        }
        public int LaySoLuongTon(string MaSP) // khac phuc tinh trang truy van sai
        {
            string sql = "SELECT SLTon FROM SANPHAM WHERE MaSP='" + MaSP + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            reader.Read();
            return reader.GetInt32(0);
        }
        public int CapNhatSoLuongSanPham(int soluong, string MaSP)
        {
            string sql = "UPDATE SANPHAM SET SLTon=" + soluong + " WHERE MaSP= '" + MaSP + "'";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int res = cmd.ExecuteNonQuery();
            conn.Close();
            return res;
        }

        public string TaoMaSPMoi()
        {
            string LastID = LayMaSPLonNhat();
            string NewID = NextID(LastID, "SP");
            return NewID;
        }
        public string NextID(string lastID, string prefixID)
        {
            if (lastID == "")
            {
                return prefixID + "001";  // fixwidth default
            }
            int nextID = int.Parse(lastID.Remove(0, prefixID.Length)) + 1;
            int lengthNumerID = lastID.Length - prefixID.Length;
            string zeroNumber = "";
            for (int i = 1; i <= lengthNumerID; i++)
            {
                if (nextID < Math.Pow(10, i))
                {
                    for (int j = 1; j <= lengthNumerID - i; i++)
                    {
                        zeroNumber += "0";
                    }
                    return prefixID + zeroNumber + nextID.ToString();
                }
            }
            return prefixID + nextID;

        }
        public string LayMaSPLonNhat() // LayMaSP lon nhat
        {
            string sql = "SELECT TOP  1, MaSP FROM SANPHAM ORDER BY MaSP DESC  ";
            OleDbConnection conn = new OleDbConnection();
            conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            SanPhamDTO spDTO = new SanPhamDTO();
            while (reader.Read())
            {

                spDTO.MaSPLN = reader.GetString(0);
            }
            conn.Close();
            return spDTO.MaSPLN;
        }
        public int ThemSanPham(SanPhamDTO spDTO)
        {
            //tao ket noi csdl
            OleDbConnection conn = DataProvider.ConnectDB();
            //viet sql
            string sql = "INSERT INTO SanPham(MaSP,TenSP,SLTon,DonGia,CV_SP) ";
            sql = sql + " VALUES(@MaSP,@TenSP,@SLTon,@DonGia,@CV_SP) ";
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para = cmd.Parameters.Add("@MaSP", OleDbType.VarChar);
            para.Value = spDTO.MaSP;
            para = cmd.Parameters.Add("@TenSP", OleDbType.VarChar);
            para.Value = spDTO.TenSP;
            para = cmd.Parameters.Add("@SLTon", OleDbType.VarChar);
            para.Value = spDTO.SLTon;
            para = cmd.Parameters.Add("@DonGia", OleDbType.VarChar);
            para.Value = spDTO.DonGia;
            para = cmd.Parameters.Add("@CV_SP", OleDbType.VarChar);
            para.Value = spDTO.CV_SP;

            int kq = cmd.ExecuteNonQuery();

            conn.Close();
            if (kq >= 0)
                return 1;
            return 0;

        }
    }

}
